{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import time\n",
    "import pandas as pd\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import django\n",
    "django.setup()\n",
    "from django.db import connections\n",
    "from firecares.firestation.models import FireDepartment, FireStation, NFIRSStatistic\n",
    "\n",
    "fdid = '20019'\n",
    "state = 'NJ'\n",
    "years = tuple(map(int, pd.read_sql_query('select distinct(extract(year from inc_date)) as year from buildingfires order by year desc;', connections['nfirs'])['year']))\n",
    "fd = FireDepartment.objects.filter(fdid=fdid, state=state).first()\n",
    "\n",
    "params = dict(fdid=fdid, state=state, years=years)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Firefighter casualty segmented by structure hazard level vs flat NFIRS statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using closest parcel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category\n",
    "FROM ffcasualty a left join (SELECT\n",
    "  *\n",
    "FROM (\n",
    "  SELECT state, \n",
    "    fdid,\n",
    "    inc_date,\n",
    "    inc_no,\n",
    "    exp_no,\n",
    "    geom,\n",
    "    b.parcel_id,\n",
    "    b.wkb_geometry,\n",
    "    b.risk_category,\n",
    "    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(b.wkb_geometry, a.geom)) AS r\n",
    "  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a\n",
    "     left join parcel_risk_category_local b on a.geom && b.wkb_geometry\n",
    "     ) x\n",
    "WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s\n",
    "GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')\n",
    "ORDER BY extract(year from inc_date) DESC\"\"\"\n",
    "    \n",
    "df = pd.read_sql_query(q, connections['nfirs'], params=params)\n",
    "\n",
    "print df\n",
    "\n",
    "print df.groupby('year').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using old non risk-aware method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "q = \"\"\"select extract(year from inc_date) as year, count(*) from ffcasualty where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;\"\"\"\n",
    "cur = connections['nfirs'].cursor()\n",
    "\n",
    "\n",
    "print pd.read_sql_query(q, connections['nfirs'], params=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using `parcel_id` join to `parcel_risk_category_local`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"SELECT count(1) as count, extract(year from a.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level\n",
    "FROM ffcasualty a\n",
    "LEFT JOIN\n",
    "    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category\n",
    "        FROM ( SELECT *\n",
    "            FROM incidentaddress a\n",
    "            LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
    "        ) AS x\n",
    "    ) AS y\n",
    "USING (state, fdid, inc_date, inc_no, exp_no)\n",
    "WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s\n",
    "GROUP BY y.risk_category, extract(year from a.inc_date)\n",
    "ORDER BY extract(year from a.inc_date) DESC\"\"\"\n",
    "\n",
    "print pd.read_sql_query(q, connections['nfirs'], params=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building fires segmented by structure hazard level vs existing NFIRS statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"select count(1), extract(year from alarm) as year, COALESCE(b.risk_category, 'N/A') as risk_category\n",
    "FROM buildingfires a left join (SELECT\n",
    "  *\n",
    "FROM (\n",
    "  SELECT state,\n",
    "    fdid,\n",
    "    inc_date,\n",
    "    inc_no,\n",
    "    exp_no,\n",
    "    geom,\n",
    "    b.parcel_id,\n",
    "    b.wkb_geometry,\n",
    "    b.risk_category,\n",
    "    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r\n",
    "  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a\n",
    "     left join parcel_risk_category_local b on a.geom && b.wkb_geometry\n",
    "     ) x\n",
    "WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from alarm) in %(years)s\n",
    "GROUP by extract(year from alarm), COALESCE(b.risk_category, 'N/A')\n",
    "ORDER BY extract(year from alarm) DESC\"\"\"\n",
    "\n",
    "print pd.read_sql_query(q, connections['nfirs'], params=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using directly-attached parcel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level\n",
    "FROM buildingfires a\n",
    "LEFT JOIN\n",
    "    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category\n",
    "        FROM ( SELECT *\n",
    "            FROM incidentaddress a\n",
    "            LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
    "        ) AS x\n",
    "    ) AS y\n",
    "USING (state, fdid, inc_date, inc_no, exp_no)\n",
    "WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s\n",
    "GROUP BY y.risk_category, extract(year from a.alarm)\n",
    "ORDER BY extract(year from a.alarm) DESC\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, connections['nfirs'], params=params))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"select extract(year from inc_date) as year, count(*) from buildingfires where fdid=%(fdid)s and state=%(state)s and extract(year from inc_date) in %(years)s group by year order by year desc;\"\"\"\n",
    "    \n",
    "print pd.read_sql_query(q, connections['nfirs'], params=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Civilian casualities segmented by structure hazard level vs existing NFIRS statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "print 'Using closest parcel'\n",
    "\n",
    "q = \"\"\"select count(1), extract(year from inc_date) as year, COALESCE(b.risk_category, 'N/A') as risk_category\n",
    "FROM civiliancasualty a left join (SELECT\n",
    "  *\n",
    "FROM (\n",
    "  SELECT state, \n",
    "    fdid,\n",
    "    inc_date,\n",
    "    inc_no,\n",
    "    exp_no,\n",
    "    geom,\n",
    "    b.parcel_id,\n",
    "    b.wkb_geometry,\n",
    "    b.risk_category,\n",
    "    ROW_NUMBER() OVER (PARTITION BY state, fdid, inc_date, inc_no, exp_no, geom ORDER BY st_distance(st_centroid(b.wkb_geometry), a.geom)) AS r\n",
    "  FROM (select * from incidentaddress where state=%(state)s and fdid=%(fdid)s) a\n",
    "     left join parcel_risk_category_local b on a.geom && b.wkb_geometry\n",
    "     ) x\n",
    "WHERE x.r = 1) b using (state, inc_date, exp_no, fdid, inc_no) where state=%(state)s and fdid=%(fdid)s and extract(year from inc_date) in %(years)s\n",
    "GROUP by extract(year from inc_date), COALESCE(b.risk_category, 'N/A')\n",
    "ORDER BY extract(year from inc_date) DESC\"\"\"\n",
    "\n",
    "print pd.read_sql_query(q, connections['nfirs'], params=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All fire-related calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"SELECT count(1) as count, extract(year from b.inc_date) as year, COALESCE(y.risk_category, 'N/A') as risk_level\n",
    "FROM fireincident b\n",
    "LEFT JOIN\n",
    "    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category\n",
    "        FROM (SELECT *\n",
    "            FROM incidentaddress a\n",
    "            LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
    "        ) AS x\n",
    "    ) AS y\n",
    "USING (state, fdid, inc_date, inc_no, exp_no)\n",
    "WHERE b.state = %(state)s AND b.fdid = %(fdid)s AND extract(year FROM b.inc_date) IN %(years)s\n",
    "GROUP BY y.risk_category, extract(year FROM b.inc_date)\n",
    "ORDER BY extract(year FROM b.inc_date) DESC\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, connections['nfirs'], params=params))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Structure fire calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level\n",
    "FROM buildingfires a\n",
    "LEFT JOIN\n",
    "    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category\n",
    "        FROM ( SELECT *\n",
    "            FROM incidentaddress a\n",
    "            LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
    "        ) AS x\n",
    "    ) AS y\n",
    "USING (state, fdid, inc_date, inc_no, exp_no)\n",
    "WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s\n",
    "GROUP BY y.risk_category, extract(year from a.alarm)\n",
    "ORDER BY extract(year from a.alarm) DESC\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, connections['nfirs'], params=params))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from firecares.tasks.update import update_nfirs_counts\n",
    "\n",
    "update_nfirs_counts.delay(fd.id)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
